package com.wb.code14JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 黄衡熙
 * @version 1.0
 */
public class Test02_SQL注入问题 {
    public static void main(String[] args) {
        List<User> users = login("1' or '1' = '1", "1' or '1' = '1"); // 登录失败
        for (User user : users) {
            System.out.println(user);
        }
    }
    public static List<User> login(String username, String password){
//      准备连接
        Connection conn = null;
//        陈述，获取状态集
        Statement st = null;
//        返回结果集
        ResultSet rs = null;
        List<User> users = new ArrayList<User>();
        try {
//            注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
//            获取连接
            conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","qq670892");
//           获取状态集
            st=conn.createStatement();
//            执行SQL
            rs=st.executeQuery("select * from t_user where username = '"+username+"'and password = '"+password+"'");
//           返回结果集
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
//        释放资源
        return users;
    }

    public static List<User> login2(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "root");
            // PreparedStatement对于sql进行预编译
            // 因此,在获取状态集对象的时候,需要存在sql
            // String sql = "select id,username,password,phone,address from t_user where username = ? and password = ?";
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" where username = ? ")
                    .append(" and password = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            // 为状态集中预编译的sql传递参数
            // 通过setXxx(index,value):为第index个参数赋值,index从1开始,xxx表示参数的类型
            // 在sql语句中存在几个问号,就需要设置几个参数
            // 设置参数的顺序与问号的顺序一致
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return users;
    }
}
